# -*- coding: utf-8 -*-

from openerp import tools, models, fields, api
import logging,json
from openerp.osv import osv
import calendar,time
import openerp.osv.fields as _fields

_logger = logging.getLogger(__name__)  

def log_info(tag, msg):
    _logger.info('****************{0}****************'.format(tag))
    _logger.info(msg)

class account_treasury_report_inherit(osv.osv):
    _inherit = "account.treasury.report"
    _description = "Treasury Analysis"
    _auto = False

    def _compute_balances(self, cr, uid, ids, field_names, arg=None, context=None,
                          query='', query_params=()):
        all_treasury_lines = self.search(cr, uid, [], context=context)
        all_companies = self.pool.get('res.company').search(cr, uid, [], context=context)
        current_sum = dict((company, 0.0) for company in all_companies)
        
        res = dict((id, dict((fn, 0.0) for fn in field_names)) for id in all_treasury_lines)
        for record in self.browse(cr, uid, all_treasury_lines, context=context):
            res[record.id]['starting_balance'] = current_sum[record.company_id.id] 
            current_sum[record.company_id.id] += record.balance
            res[record.id]['ending_balance'] = current_sum[record.company_id.id]
        return res    

    _order = 'date asc'
    
    _columns = {
        'account_id': _fields.many2one('account.account', 'Account', readonly=True)
    }    


    def init(self, cr):
        tools.drop_view_if_exists(cr, 'account_treasury_report')
        cr.execute("""
            create or replace view account_treasury_report as (
            select
                p.id as id,
                p.fiscalyear_id as fiscalyear_id,
                p.id as period_id,
                l.debit as debit,
                l.credit as credit,
                l.debit-l.credit as balance,
                am.date as date,
                l.account_id as account_id,
                am.company_id as company_id
            from
                account_move_line l
                left join account_account a on (l.account_id = a.id)
                left join account_move am on (am.id=l.move_id)
                left join account_period p on (am.period_id=p.id)
            where l.state != 'draft'
              and a.user_type = 30 
            )
        """)   
        '''group by p.id, p.fiscalyear_id, p.date_start, am.company_id, l.account_id'''
        
        
class account_test(models.Model):
    _name = 'lmch.account.test2'
    
    user_type = fields.Many2one('account.account.type', '科目类型')
    period_id = fields.Many2one('account.period', '会计期间')
    debit = fields.Float('收入')
    credit = fields.Float('支出')
    balance = fields.Float('余额') 
    date = fields.Date('有效时间')
    company_id = fields.Many2one('res.company', '公司')
    account_id = fields.Many2one('account.account', '科目')
    fiscalyear_id = fields.Many2one('account.fiscalyear', '会计年度') 
    starting_balance = fields.Float('期初余额')
    ending_balance = fields.Float('期末余额')
    
    def search(self, cr, uid, args, offset=0, limit=None, order=None,
               context=None, count=False):
        _logger.info('####################search')
        return super(account_test, self).search(cr, uid, args=args, offset=offset, limit=limit, order=order,
                                                          context=context, count=count)   
    
    
    def read_group(
        self, cr, uid, domain, fields, groupby, offset=0, 
        limit=None, context=None, orderby=False,lazy=True
        ):
        log_info('domain', domain)
        log_info('groupby', groupby)
        log_info('context', context)
        
        month = int(time.strftime('%m'))
        year = int(time.strftime('%Y'))
        end_day = calendar.monthrange(year, month)[1]  
        
        
        if context.get('period', False) == 'current_period':
            #for d in ['&',('date','<=', time.strftime('%Y-%m-'+str(end_day))),
                           #('date','>=',time.strftime('2016-01-01'))]:
            #domain.append(['date', '=', time.strftime('%Y-%m-%d')])
            domain.append(['date', '=', time.strftime('2017-03-04')])
            log_info('_domain', domain)
        
        return super(account_test, self).read_group(cr, uid, domain,
                                                              fields, groupby, 
                                                              offset, limit, 
                                                              context, orderby,
                                                              lazy)  
                                                              
    #def search(self, cr, uid, args, offset=0, limit=None, order=None,
               #context=None, count=False):
        #fiscalyear_obj = self.pool.get('account.fiscalyear')
        #period_obj = self.pool.get('account.period')
        #for arg in args:
            #if arg[0] == 'period_id' and arg[2] == 'current_period':
                #current_period = period_obj.find(cr, uid, context=context)[0]
                #args.append(['period_id','in',[current_period]])
                #break
            #elif arg[0] == 'period_id' and arg[2] == 'current_year':
                #current_year = fiscalyear_obj.find(cr, uid)
                #ids = fiscalyear_obj.read(cr, uid, [current_year], ['period_ids'])[0]['period_ids']
                #args.append(['period_id','in',ids])
        #for a in [['period_id','in','current_year'], ['period_id','in','current_period']]:
            #if a in args:
                #args.remove(a)
        #return super(account_test, self).search(cr, uid, args=args, offset=offset, limit=limit, order=order,
                                                          #context=context, count=count)   
    
    #def init(self, cr):
        #sql = '''
        #select * from lmch_account_test2
        #'''
        #_logger.info('*********************sql########')
        #cr.execute(sql)
        
        #for r in cr.dictfetchall():
            #if not r.has_key('starting_balance'):
                #_logger.info(r)
                
        # 以科目(货币资金类型)作为基本单位
        #cr.execute('''
        #DROP table IF EXISTS lmch_account_test1 CASCADE
        #''')        
        #field_names = ['starting_balance', 'ending_balance']
        #all_move_lines = self.pool.get('lmch.currency.fund.report').search(cr, 1, [])
        ## account.company_id 所属公司, many2one
        #all_accounts = self.pool.get('account.account').search(cr, 1, [('user_type', '=', 30)])
        
        ##recs = self.pool.get('lmch.currency.fund.report').read(
            ##cr, 1, all_move_lines[-60:-1]
        ##)
        ##_logger.info(json.dumps(recs, indent=4))
        
        #current_sum = dict((account_id, 0.0) for account_id in all_accounts)
        
        #res = {}
        #for id in all_move_lines:
            #for fn in field_names:
                #res[id] = {fn:0.0}
        
        #_logger.info('******************time1')
        #for record in self.pool.get('lmch.currency.fund.report').browse(cr, 1, all_move_lines):
            #res[record.id]['starting_balance'] = current_sum[record.account_id.id] 
            #current_sum[record.account_id.id] += record.balance
            #res[record.id]['ending_balance'] = current_sum[record.account_id.id]    
            #self.create(cr, 1, {
                #'user_type':record.user_type.id,
                #'period_id':record.period_id.id,
                #'debit':record.debit,
                #'credit':record.credit,
                #'balance':record.balance,
                #'date':record.date,
                #'company_id':record.company_id.id,
                #'account_id':record.account_id.id,
                #'fiscalyear_id':record.fiscalyear_id.id,
                #'starting_balance':res[record.id]['starting_balance'],
                #'ending_balance':res[record.id]['ending_balance']
            #})
        #_logger.info('******************time2')
        ##_logger.info(json.dumps(res, indent=4))   
        
#class account_test_view(models.Model):
    #_name = 'lmch.account.test.view'
    
    #user_type = fields.Many2one('account.account.type', '科目类型')
    #period_id = fields.Many2one('account.period', '会计期间')
    #debit = fields.Float('收入')
    #credit = fields.Float('支出')
    #balance = fields.Float('余额') 
    #date = fields.Date('有效时间')
    #company_id = fields.Many2one('res.company', '公司')
    #account_id = fields.Many2one('account.account', '科目')
    #fiscalyear_id = fields.Many2one('account.fiscalyear', '会计年度') 
    #starting_balance = fields.Float('期初余额')
    #ending_balance = fields.Float('期末余额')
    
    #_auto = False
    
    #def init(self, cr):
        
        #cr.execute('''
        #DROP view IF EXISTS lmch_account_test_view CASCADE
        #''')
        #sql = '''
        #create or replace view lmch_account_test_view as (
        #select 
          #l.date as date,
          #l.period_id as period_id,
          #l.user_type as user_type,
          #l.debit as debit,
          #l.credit as credit,
          #l.account_id as account_id,
          #l.company_id as company_id,
          #l.fiscalyear_id as fiscalyear_id,
          #l.balance as balance,
          #l.starting_balance as starting_balance,
          #l.ending_balance as ending_balance
        #from 
          #lmch_account_test2 l 
        #)      
        #'''
        #cr.execute(sql)
        #_logger.info('$$$$$$$$$$$$$$$$$$$$$$$$$')

class account_entries_report(models.Model):
    
    _name = 'lmch.currency.fund.report'
    
    
    user_type = fields.Many2one('account.account.type', '科目类型', readonly=True)
    period_id = fields.Many2one('account.period', '会计期间', readonly=True)
    debit = fields.Float('收入', readonly=True)
    credit = fields.Float('支出', readonly=True)
    balance = fields.Float('余额', readonly=True) 
    date = fields.Date('有效时间', readonly=True)
    company_id = fields.Many2one('res.company', '公司', readonly=True)
    account_id = fields.Many2one('account.account', '科目', readonly=True)
    fiscalyear_id = fields.Many2one('account.fiscalyear', '会计年度', readonly=True)
    
    _auto = False
    _order = 'date asc'
    
    
    
    def search(self, cr, uid, args, offset=0, limit=None, order=None,
               context=None, count=False):
        fiscalyear_obj = self.pool.get('account.fiscalyear')
        period_obj = self.pool.get('account.period')
        for arg in args:
            if arg[0] == 'period_id' and arg[2] == 'current_period':
                current_period = period_obj.find(cr, uid, context=context)[0]
                args.append(['period_id','in',[current_period]])
                break
            elif arg[0] == 'period_id' and arg[2] == 'current_year':
                current_year = fiscalyear_obj.find(cr, uid)
                ids = fiscalyear_obj.read(cr, uid, [current_year], ['period_ids'])[0]['period_ids']
                args.append(['period_id','in',ids])
        for a in [['period_id','in','current_year'], ['period_id','in','current_period']]:
            if a in args:
                args.remove(a)
        return super(account_entries_report, self).search(cr, uid, args=args, offset=offset, limit=limit, order=order,
                                                          context=context, count=count)    
    
    def read_group(
        self, cr, uid, domain, fields, groupby, offset=0, 
        limit=None, context=None, orderby=False,lazy=True
        ):
      
        if context is None:
            context = {}
        fiscalyear_obj = self.pool.get('account.fiscalyear')
        period_obj = self.pool.get('account.period')
        if context.get('period', False) == 'current_period':
            current_period = period_obj.find(cr, uid, context=context)[0]
            domain.append(['period_id','in',[current_period]])
        elif context.get('year', False) == 'current_year':
            current_year = fiscalyear_obj.find(cr, uid)
            ids = fiscalyear_obj.read(cr, uid, [current_year],
                                      ['period_ids'])[0]['period_ids']
            domain.append(['period_id','in',ids])
        else:
            domain = domain
        return super(account_entries_report, self).read_group(cr, uid, domain,
                                                              fields, groupby, 
                                                              offset, limit, 
                                                              context, orderby,
                                                              lazy)
    
    
    def init(self, cr):
            
        cr.execute('''
        DROP view IF EXISTS lmch_currency_fund_report CASCADE
        ''')
        sql = '''
        create or replace view lmch_currency_fund_report as (
        select 
          l.id as id,
          am.date as date,
          am.period_id as period_id,
          a.user_type as user_type,
          l.debit as debit,
          l.credit as credit,
          l.account_id as account_id,
          am.company_id as company_id,
          p.fiscalyear_id as fiscalyear_id,
          coalesce(l.debit, 0.0) - coalesce(l.credit, 0.0) as balance
        from 
          account_move_line l  
          left join account_account a on (l.account_id = a.id)
          left join account_move am on (am.id=l.move_id)
          left join account_period p on (am.period_id=p.id)
        where
          user_type = 30 and l.state != 'draft'
        )
        '''
        cr.execute(sql)
        _logger.info('***************************************')
        